/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package DataAccess;

import Entity.Category;
import Entity.EAccount;
import Entity.InfoDatabase;
import Entity.Items;
import com.sun.rowset.CachedRowSetImpl;
import java.awt.Color;
import java.awt.Component;
import java.lang.reflect.Array;
import java.sql.Date;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.DefaultComboBoxModel;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JTable;
import javax.swing.event.TableModelEvent;
import javax.swing.table.DefaultTableModel;
import javax.swing.table.TableCellRenderer;

/**
 *
 * @author dung
 */
public class AccessDB {
    InfoDatabase idb = DBUtil.getInfoDB();
    public CachedRowSetImpl crsi (String s){
        CachedRowSetImpl rs = null;
        try{
                rs=new CachedRowSetImpl();
                rs.setUsername(idb.getUser());
                rs.setPassword(idb.getPass());
                rs.setUrl("jdbc:sqlserver://"+idb.getServer()+":"+idb.getPort()+";databaseName="+idb.getDatabase());
                rs.setCommand(s);

            }
            catch(Exception e){
                //
            }

            return rs;
    }
    public void InsertAccount(EAccount eacc){
        if(eacc!=null)
        {
            try{
                CachedRowSetImpl rscheck = crsi("select * from Account Where Username ='"+eacc.getUsername()+"'");
                rscheck.execute();
                if(rscheck.next())
                {
                    JOptionPane.showMessageDialog(null, "Username not available", "Error", JOptionPane.ERROR_MESSAGE);
                }
                else{
                CachedRowSetImpl rs = crsi("Insert into Account (Username,Password,FullName,DayOfBirth,Gender,Address,Phone,Email,RoleID) values (?,?,?,?,?,?,?,?,?)");
                rs.setString(1, eacc.getUsername());
                rs.setString(2, eacc.getPassword());
                rs.setString(3, eacc.getFullName());
                Date det = new Date(eacc.getDOB().getTime());
                rs.setDate(4, det);
                rs.setBoolean(5, eacc.isSex());
                rs.setString(6, eacc.getAddress());
                rs.setString(7, eacc.getPhone());
                rs.setString(8, eacc.getEmail());
                rs.setString(9, eacc.getRoleID());
                rs.execute();
                }
        }   catch (SQLException ex) {
                //
            }
    }
}
    public void RefeshAccount(JTable model)
    {
        DefaultTableModel dm = (DefaultTableModel) model.getModel();
            for (int i = dm.getRowCount() - 1; i >= 0; i--) {
                dm.removeRow(i);
            }
        SelectAccount(dm);
    }
    public void RefeshItem(JTable model)
    {
        DefaultTableModel dm = (DefaultTableModel) model.getModel();
            for (int i = dm.getRowCount() - 1; i >= 0; i--) {
                dm.removeRow(i);
            }
        SelectItems(dm);
    }
    public void RefeshCate(JTable model)
    {
        DefaultTableModel dm = (DefaultTableModel) model.getModel();
            for (int i = dm.getRowCount() - 1; i >= 0; i--) {
                dm.removeRow(i);
            }
        SelectCategories(dm);
    }
     public void updateAccount(EAccount eacc){
        if(eacc!=null)
        {
            try{
                CachedRowSetImpl rs = crsi("update Account set FullName =?,DayOfBirth=?,Gender=?,Address=?,Phone=?,Email=?,RoleID=? where Username=?");
                rs.setString(1, eacc.getFullName());
                Date det = new Date(eacc.getDOB().getTime());
                rs.setDate(2, det);
                rs.setBoolean(3, eacc.isSex());
                rs.setString(4, eacc.getAddress());
                rs.setString(5, eacc.getPhone());
                rs.setString(6, eacc.getEmail());
                rs.setString(7, eacc.getRoleID());
                rs.setString(8, eacc.getUsername());
                int nResult = JOptionPane.showConfirmDialog(null, "Are you sure to edit Account?", "edit Account", JOptionPane.YES_NO_OPTION);
                if(nResult==JOptionPane.YES_OPTION)
                {
                    rs.execute();

//                    Object[] rowData = {irefer.getId(),irefer.getFullname(),irefer.getWorkname(),sdf.format(irefer.getDob()),irefer.getAddress(),irefer.getPhone()};
//                    model.addRow(rowData);
                }
        }   catch (SQLException ex) {
                //
            }
    }
}
//     public void DeleteAccount(String user,int iRow)
//        {
//        try {
//            CachedRowSetImpl rs = crsi("delete Account Where username=?");
//            rs.setString(1, user);
//            int nResult = JOptionPane.showConfirmDialog(null, "Are you sure to delete Account?", "Delete Account", JOptionPane.YES_NO_OPTION);
//            if(nResult==JOptionPane.YES_OPTION)
//            {
//                rs.execute();
//                model.removeRow(iRow);
//            }
//        } catch (SQLException ex) {
//            Logger.getLogger(AccessDB.class.getName()).log(Level.SEVERE, null, ex);
//        }
//
//        }
     public void LockAccount(boolean active,String account,String pass,String message,String title)
        {
            
            CachedRowSetImpl rs = null;
            if(pass==null){
            try {
                rs = crsi("update Account set Active = ? where Username=?");
                rs.setBoolean(1, active);
                rs.setString(2, account);
            } catch (SQLException ex) {
                Logger.getLogger(AccessDB.class.getName()).log(Level.SEVERE, null, ex);
            }

            }
            else if(pass!=null)
            {
            try {
                rs = crsi("update Account set Password = ?, ChangedPass='"+false+"' where Username=?");
                rs.setString(1, pass);
                rs.setString(2, account);
            } catch (SQLException ex) {
                Logger.getLogger(AccessDB.class.getName()).log(Level.SEVERE, null, ex);
            }
            }
        int nResult = JOptionPane.showConfirmDialog(null, message, title, JOptionPane.YES_NO_OPTION);
                if(nResult==JOptionPane.YES_OPTION)
                {
            try {
                rs.execute();
            } catch (SQLException ex) {
                JOptionPane.showMessageDialog(null, ex, "Error", JOptionPane.ERROR_MESSAGE);
            }
               }
        }
     public void LoadReport(int AccountID, DefaultTableModel model)
        {
            int iRows = model.getRowCount();
             if(iRows > 0){
             for (int i = 0; i<iRows;i++) {
                model.removeRow(0);
            }}
            CachedRowSetImpl rs = crsi("Select ReportID, UserName, dateReport,"
                    + " Title, status from Report inner join Account on Report.fromSales = Account.AccountID "
                    + "where ToManager=? order by dateReport,status");
            
        try {
            rs.setInt(1, AccountID);
            rs.execute();
            Vector cols = new Vector();
            cols.addElement("ReportID");
            cols.addElement("From");
            cols.addElement("Date");
            cols.addElement("Title");
            cols.addElement("Status");
            model.setColumnIdentifiers(cols);

            while(rs.next())
            {
                Vector e = new Vector();
                e.addElement(rs.getInt("reportID"));
                e.addElement(rs.getString("UserName"));
                e.addElement(rs.getDate("dateReport"));
                e.addElement(rs.getString("title"));
                e.addElement(rs.getBoolean("Status"));
                model.addRow(e);
            }
        } catch (SQLException ex) {
            Logger.getLogger(AccessDB.class.getName()).log(Level.SEVERE, null, ex);
        }
        }
     public String ReportCount(String report,int AccountID){
         int countNews =0;
         int Count =0;
        CachedRowSetImpl rs = crsi("Select ReportID, UserName, dateReport,"
                    + " Title, status from Report inner join Account on Report.fromSales = Account.AccountID "
                    + "where ToManager=? order by dateReport asc");
        try {
            rs.setInt(1, AccountID);
            rs.execute();
            
//        else{
            while(rs.next())
            {
                Count+=1;
                if(rs.getBoolean("status")==false)
                    countNews+=1;
            }
            report="You have "+countNews+"/"+Count+" Reports";
//            if(!rs.next())
//            {
//                report="You have no Report";
//            }
//        }
        } catch (SQLException ex) {
            Logger.getLogger(AccessDB.class.getName()).log(Level.SEVERE, null, ex);
        }
        return report;
     }
     public void loadItemReceipt(int billId,DefaultTableModel model)
        {
         int iRows = model.getRowCount();
         if(iRows > 0){
         for (int i = 0; i<iRows;i++) {
                model.removeRow(0);
            }}
        try {
            CachedRowSetImpl rs = crsi("select BillId, ItemName, "
                      + "Quantity, QuantityReceipt from Items inner join "
                      + "Billselldetails on billselldetails.itemid=items.itemid "
                      + "where billselldetails.billid=?");
            rs.setInt(1, billId);
            rs.execute();
            ResultSetMetaData rsmeta = rs.getMetaData();
            Vector cols = new Vector();

            for(int i= 0;i< rsmeta.getColumnCount() ;i++)
            {
                cols.add(rsmeta.getColumnName(i+1));
            }
            model.setColumnIdentifiers(cols);
            while(rs.next())
            {
                Vector e = new Vector();
              
                e.addElement(rs.getInt("BillID"));
                e.addElement(rs.getString("ItemName"));
                e.addElement(rs.getInt("Quantity"));
                e.addElement(rs.getInt("QuantityReceipt"));
                model.addRow(e);
            }
        } catch (SQLException ex) {
            Logger.getLogger(AccessDB.class.getName()).log(Level.SEVERE, null, ex);
        }
        }
     public void LoadReceipt(DefaultTableModel model){
         int iRows = model.getRowCount();
         if(iRows > 0){
         for (int i = 0; i<iRows;i++) {
                model.removeRow(0);
            }}
         try {
            CachedRowSetImpl rs = crsi("select * from receipt order by valid asc");
            rs.execute();
            ResultSetMetaData rsmeta = rs.getMetaData();
            Vector cols = new Vector();
            for(int i= 0;i< rsmeta.getColumnCount() ;i++)
            {
                cols.add(rsmeta.getColumnName(i+1));
            }
            model.setColumnIdentifiers(cols);
            while(rs.next())
            {
                Vector e = new Vector();
                e.addElement(rs.getInt("ReceiptID"));
                e.addElement(rs.getInt("BillID"));
                e.addElement(rs.getDate("date"));
                e.addElement(rs.getBoolean("valid"));
                e.addElement(rs.getBoolean("accept"));
                model.addRow(e);
            }
//            table.setModel(model);
//            int iCounts = table.getRowCount();
//            Color tableColor = new Color(152);
//            for(int i =0;i<iCounts;i++)
//            {
//                if(String.valueOf(table.getValueAt(i, 3)).equalsIgnoreCase(""))
//            }
            //setBackground(tableColor);
           
        } catch (SQLException ex) {
            Logger.getLogger(AccessDB.class.getName()).log(Level.SEVERE, null, ex);
        }
     }
     public void RefeshReceipt(JTable model)
    {
        DefaultTableModel dm = (DefaultTableModel) model.getModel();
            for (int i = dm.getRowCount() - 1; i >= 0; i--) {
                dm.removeRow(i);
            }
        LoadReceipt(dm);
    }
     public void SelectAccount(DefaultTableModel model)
    {
        try{
            CachedRowSetImpl rs = crsi("select * from Account order by RoleID");
            rs.execute();
            ResultSetMetaData rsmeta = rs.getMetaData();
            Vector cols = new Vector();
            for(int i= 0;i< rsmeta.getColumnCount() ;i++)
            {
                if(i!=2){
                cols.add(rsmeta.getColumnName(i+1));
                }
                }
            model.setColumnIdentifiers(cols);
//            for (int i = model.getRowCount() - 1; i >= 0; i--) {
//                model.removeRow(i);
//            }
            while(rs.next())
            {
                Vector e = new Vector();
                e.addElement(rs.getInt(1));
                e.addElement(rs.getString(2));
                e.addElement(rs.getString(4));
                e.addElement(rs.getDate(5));
                if(rs.getBoolean(6)==true){
                e.addElement("Male");}
                else
                {
                    e.addElement("Female");
                }
                e.addElement(rs.getString(7));
                e.addElement(rs.getString(8));
                e.addElement(rs.getString(9));
                e.addElement(rs.getString(10));
                e.addElement(rs.getBoolean(11));
                e.addElement(rs.getBoolean(12));
                model.addRow(e);
            }
        }catch(Exception ex){
            
        }
    }
     public void SelectCategories(DefaultTableModel model)
    {
        try{
            CachedRowSetImpl rs = crsi("select * from Categories");
            rs.execute();
            ResultSetMetaData rsmeta = rs.getMetaData();
           Vector cols = new Vector();
            for(int i= 0;i< rsmeta.getColumnCount() ;i++)
            {
                cols.add(rsmeta.getColumnName(i+1));
                }
            model.setColumnIdentifiers(cols);
            while(rs.next())
            {
                Vector e = new Vector();
                e.addElement(rs.getInt(1));
                e.addElement(rs.getString(2));
                e.addElement(rs.getBoolean(3));
                model.addRow(e);
            }
        }catch(Exception ex){

        }
    }
     public void SelectItems(DefaultTableModel model)
    {
        try{
            CachedRowSetImpl rs = crsi("select * from Items order by categoryID");
            rs.execute();
            ResultSetMetaData rsmeta = rs.getMetaData();
            Vector cols = new Vector();
            for(int i= 0;i< rsmeta.getColumnCount() ;i++)
            {
                cols.add(rsmeta.getColumnName(i+1));

                }
            model.setColumnIdentifiers(cols);
            while(rs.next())
            {
                Vector e = new Vector();
                e.addElement(rs.getInt(1));
                e.addElement(rs.getInt(2));
                e.addElement(rs.getString(3));
                e.addElement(rs.getInt(4));
                e.addElement(rs.getFloat(5));
                e.addElement(rs.getString(6));
                e.addElement(rs.getString(7));
                e.addElement(rs.getString(8));
                e.addElement(rs.getBoolean(9));
		
                model.addRow(e);
            }
        }catch(Exception ex){

        }
    }
     public void InsertItems(Items items){
        if(items!=null)
        {
            try {
                CachedRowSetImpl rscheck = crsi("select * from items Where itemname ='" + items.getItemName() + "'");
                rscheck.execute();
                if (rscheck.next()) {
                    JOptionPane.showMessageDialog(null, "Item name existed", "Error", JOptionPane.ERROR_MESSAGE);
                }
                CachedRowSetImpl rs = crsi("insert into Items (CategoryID,ItemName,Price,Descript,Manufacture,Unit) values(?,?,?,?,?,?)");
                rs.setInt(1, items.getCategoryID());
                rs.setString(2, items.getItemName());
                rs.setFloat(3, items.getPrice());
                rs.setString(4, items.getDescript());
                rs.setString(5, items.getManufacture());
                rs.setString(6, items.getUnit());
//                rs.setBoolean(7, items.isStatus());
                rs.execute();
                JOptionPane.showMessageDialog(null, "Add Item Successful", "Add Item", JOptionPane.INFORMATION_MESSAGE);
            } catch (SQLException ex) {
                JOptionPane.showMessageDialog(null, ex, "Error", JOptionPane.INFORMATION_MESSAGE);
            }
        }
     }
     public void UpdateItem(Items items){
        if(items!=null)
        {
            try {

                CachedRowSetImpl rs = crsi("update Items set CategoryID=?,Price=?,Descript=?,Manufacture=? where ItemName=?");
                rs.setInt(1, items.getCategoryID());
                rs.setFloat(2, items.getPrice());
                rs.setString(3, items.getDescript());
                rs.setString(4, items.getManufacture());
                rs.setString(5, items.getItemName());
                 int nResult = JOptionPane.showConfirmDialog(null, "Are you sure to Edit Item?", "Edit Item", JOptionPane.YES_NO_OPTION);
                            if(nResult==JOptionPane.YES_OPTION)
                            {
                                rs.execute();
                                JOptionPane.showMessageDialog(null, "Edit Item Successful", "Edit Item", JOptionPane.INFORMATION_MESSAGE);
                            }
                JOptionPane.showMessageDialog(null, "Edit Item Successful", "Edit Item", JOptionPane.INFORMATION_MESSAGE);
            } catch (SQLException ex) {
                JOptionPane.showMessageDialog(null, ex, "Error", JOptionPane.INFORMATION_MESSAGE);
            }
        }
     }
     public void InsertCate(Category cate)
        {
           if(cate!=null)
        {
            try {
                CachedRowSetImpl rscheck = crsi("select * from Categories Where CategoryName ='" + cate.getCategoryName() + "'");
                rscheck.execute();
                if (rscheck.next()) {
                    JOptionPane.showMessageDialog(null, "Category name existed", "Error", JOptionPane.ERROR_MESSAGE);
                }
                CachedRowSetImpl rs = crsi("insert into Categories (CategoryName) values(?)");
                rs.setString(1, cate.getCategoryName());
                rs.execute();
                JOptionPane.showMessageDialog(null, "Add Category Successful", "Add Category", JOptionPane.INFORMATION_MESSAGE);
                }catch(Exception ex){
                JOptionPane.showMessageDialog(null, ex, "Error", JOptionPane.INFORMATION_MESSAGE);
                }
            }
}
     public void UpdateCate(Category cate)
        {
            if(cate!=null)
        {
            try {

                CachedRowSetImpl rs = crsi("update Categories set CategoryName=? where CategoryID=?");
                rs.setString(1, cate.getCategoryName());
                rs.setInt(2, cate.getCategoryID());
                 int nResult = JOptionPane.showConfirmDialog(null, "Are you sure to Edit Category?", "Edit Category", JOptionPane.YES_NO_OPTION);
                            if(nResult==JOptionPane.YES_OPTION)
                            {
                                rs.execute();
                                JOptionPane.showMessageDialog(null, "Edit Item Successful", "Edit Item", JOptionPane.INFORMATION_MESSAGE);
                            }
                
            } catch (SQLException ex) {
                JOptionPane.showMessageDialog(null, ex, "Error", JOptionPane.INFORMATION_MESSAGE);
            }
        }
        }
}

